/* Copyright(c) 2010- Siro-info Co.(http://www.siro-info.com)
 * All rights reserved.
 */
package com.siro.pm.utils;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Class Declaration:
 * 
 * @version v1.0
 * @since DatabaseOperation.java v1.0
 */
public class DatabaseOperation {
	private static final Logger logger = LoggerFactory
			.getLogger(DatabaseOperation.class);

	public static void importSqlScript(Connection conn, String file)
			throws Exception {
		importSqlScript(conn, new FileInputStream(new File(file)));
	}

	public static void importSqlScript(Connection conn, InputStream stream)
			throws Exception {
		StringBuffer sb = new StringBuffer();
		List<String> sqls = new ArrayList<String>();
		BufferedReader br = null;
		try {
			br = new BufferedReader(new InputStreamReader(stream, "UTF-8"));
			String tmp = "";
			while ((tmp = br.readLine()) != null) {
				if (tmp.startsWith("--")) {
					// 注释行不做处理
					continue;
				}
				if (tmp.indexOf(";") != -1) {
					sb.append(tmp.substring(0, tmp.indexOf(";"))).append("\n");
					sqls.add(sb.toString());
					sb = new StringBuffer();
				} else {
					sb.append(tmp).append("\n");
				}
			}
		} finally {
			br.close();
		}
		DatabaseOperation.excuteBatchSQL(conn, sqls);
	}

	/**
	 * 执行查询 <br/>
	 * 返回结果集行从 1 开始，列从 0 开始；<br/>
	 * 行号作为key，行作为value。
	 * 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public static Map<Integer, List<String>> excuteSelectSQL(Connection conn,
			String sql) throws Exception {
		logger.debug("执行查询,SQL:{}", sql);
		Map<Integer, List<String>> result = new HashMap<Integer, List<String>>();
		List<String> tmpList = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			int columnCount = rs.getMetaData().getColumnCount();
			int rowIndex = 1;
			while (rs.next()) {
				tmpList = new ArrayList<String>();
				for (int i = 1; i <= columnCount; i++)
					tmpList.add(rs.getString(i));
				result.put(rowIndex, tmpList);
				rowIndex++;
			}
		} catch (Exception e) {
			logger.error("SQL语句执行错误，SQL:" + sql + "\n" + e.getMessage(), e);
			throw e;
		} finally {
			try {
				// result set
				if (rs != null) {
					rs.close();
					rs = null;
				}
				// statement
				if (st != null) {
					st.close();
					st = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException sqle) {
				logger.error(sqle.getMessage(), sqle);
			}
		}
		return result;
	}

	/**
	 * 执行增删改
	 * 
	 * @param sql
	 * @throws Exception
	 */
	public static int excuteUpdateSQL(Connection conn, String sql)
			throws Exception {
		logger.debug("执行修改,SQL:" + sql);
		PreparedStatement ps = null;
		int count = 0;
		try {
			ps = conn.prepareStatement(sql);
			count = ps.executeUpdate();
		} catch (Exception e) {
			conn.rollback();
			logger.error("SQL语句执行错误，SQL:" + sql + "\n" + e.getMessage(), e);
			throw e;
		} finally {
			try {
				if (ps != null) {
					ps.close();
					ps = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException sqle) {
				logger.error(sqle.getMessage(), sqle);
			}
		}
		return count;
	}

	/**
	 * 执行批量SQL
	 * 
	 * @param sql
	 * @throws Exception
	 */
	public static void excuteBatchSQL(Connection conn, List<String> scripts)
			throws Exception {
		if (logger.isDebugEnabled()) {
			StringBuffer sb = new StringBuffer();
			for (String sql : scripts) {
				sb.append(sql);
			}
			logger.debug("执行脚本:\n" + sb.toString());
		}
		Statement st = null;

		try {
			st = conn.createStatement();
			for (String sql : scripts) {
				st.addBatch(sql);
			}
			st.executeBatch();
		} catch (Exception e) {
			conn.rollback();
			logger.error("SQL语句执行错误，SQL:" + scripts + "\n" + e.getMessage(), e);
			throw e;
		} finally {
			try {
				if (st != null) {
					st.close();
					st = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException sqle) {
				logger.error(sqle.getMessage(), sqle);
			}
		}
	}
}
